﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using sBlog.Net.Domain.Entities;

namespace sBlog.Net.Domain.Concrete
{
    public class DBFunction
    {
        public static List<ZoneEntity> ZoneGetList()
        {
            string strConn = ConfigurationManager.ConnectionStrings["AppDb"].ConnectionString;
            List<ZoneEntity> lst = new List<ZoneEntity>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"GetListZone";
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ZoneEntity tbl = new ZoneEntity();
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<CategoriesZoneEntity> ZoneCategoriesByZone(int zoneId)
        {
            string strConn = ConfigurationManager.ConnectionStrings["AppDb"].ConnectionString;
            List<CategoriesZoneEntity> lst = new List<CategoriesZoneEntity>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"GetCategoriesByZone";
                    cmd.Parameters.AddWithValue("@zoneId", zoneId);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        CategoriesZoneEntity tbl = new CategoriesZoneEntity();
                        tbl.CatId = int.Parse(rs["CatId"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.CatDescription = rs["CatDescription"].ToString();
                        tbl.CatName = rs["CatName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }
    }
}